QueryResult containing the details of the query. This contains a TAuthenticate indicating the status of the call, a string array of the name of each data column, and a string array of string arrays, containing the data for each row and column

The function allows the specification of criteria for filtering, sorting, what data to return, and any query options. It returns a jagged string array of results. Each defined VETtrak entity has a set of allowed fields, which can be used for filtering, sorting and/or returning. 

The entityName must be the name of a valid VETtrak entity to get data for, such as "Client", "Employer", "Enrolment" or "Contract". Call GetAdditionalDataEntityNames for a list of valid entities, and GetAdditionalDataFieldsForEntity for a list of valid fields for an entity, or refer to the additional data documentation at http://www.vettrak.com.au/api 

The filterCriteria array defines a set of criteria for filtering the data. Each element consists of a valid field name for the entity, a comparison operator (which must be "Equals", "LessThan", "GreaterThan", "LessThanOrEqualTo", "GreaterThanOrEqualTo", "NotEqualTo", "StartsWith" or "Contains"), and a value to filter with. Dates should be in the format "yyyy-mm-dd". Numeric fields must have numbers only (decimal fields may include a decimal point). An empty string can only be used with Equals and NotEqualTo operators, and will match empty or null field values in the database. String comparing will not be case sensitive. "StartsWith" and "Contains" are only valid for string fields. By default, each element in the filterCriteria array is ANDed together, meaning that a record must satisfy the condition in every filterCriteria array element to be returned. An option of "any_filter_criteria" can be specified to instead OR the filterCriteria, meaning that a record will be returned if it satisfies any condition in the filterCriteria array. 

The sortCriteria array defines the names of the fields to sort the results by, and each field can also specify ascending or decending order. The returned data will be sorted in the order of the elements in the sortCriteria array. All fields present in the sortCriteria array must also appear in the returnFields array. 

The returnFields array defines the names of the data fields to include in the result. Fields can also have an aggregation function specified ("Count", "Sum", "Max", "Min" or "Average"), with which to summarise the data. If there is at least one returnFields element with an aggregation, every field without an aggregation will be grouped, in the order they appear in the returnFields array. "Average" and "Sum" are only valid for numeric fields. If the same field is repeated more than once, each repetition must have a different aggregate, and the same field may not appear more than once both with and without an aggregate. 

The options parameter is a comma-separated string of options to apply to change the default behaviour. Valid options are: 

• any_filter_criteria: Specifies to return records that match any of the filter criteria (OR), instead of the default of all the filter criteria (AND).
• unique_records: Specifies to only return unique results. If the returnFields and filterCriteria cause duplicate records in the result, the duplicates are removed. This is equivalent to an SQL DISTINCT.

The data is returned in a QueryResult object. It contains a Fields property which is a string array listing the names of each returned column. It also contains a Values property which is a two-dimensional jagged array containing the resultant data. The first dimension of the array is each data row, and the second dimension is each data column, in the same order as the Fields array. Null values in the database are represented by empty strings in the results. Dates are in the format "yyyy-mm-dd". Datetimes are in the format "yyyy-mm-dd hh:mm:ss". Decimal fields are formatted with two decimal places. 

A token is passed for authentication. If the authentication fails, or other errors occur, no data will be returned. The result of the authentication is represented by a TAuthenticate object. 

To use this function, a database configuration XML file must be available. By default, this file is called DatabaseMapping.xml and it is located in the same directory as the ASMX files for the API(s). The name and location of this file can be changed by setting a DatabaseMappingConfigFile key in the appSettings section of the web.config file. IIS needs read access to this file. 

This function has security level "Protected", and can only be accessed by users that have explicitly been granted permission to the API.

To get a count of how many male staff members created since 01/01/2010 live in each suburb and state, ordered by state decending then suburb, you could specify the following: 

entityName = "Client"
 
filterCriteria = array of three FilterCriteria objects, containing:
    Field = "Gender", Operator = "Equals", value = "M"
    Field = "StaffFlag", Operator = "Equals", value = "Y"
    Field = "CreatedDate", Operator = "GreaterThanOrEqualTo", value = "2010-01-01"
    
sortCriteria = array of two SortCriteria objects, containing:
    Field = "ResidentialStateCode", Order = "Descending"
    Field = "ResidentialSuburb"
    
returnFields = array of three ReturnField objects, containing:
    Field = "ResidentialSuburb"
    Field = "ResidentialStateCode"
    Field = "ClientCode", Aggregate = "Count"

An example of the return value for this function is: 

QueryResults object containing:
    Auth = TAuthenticate object
    Fields = a string array of:
        [0] = "ResidentialSuburb"
        [1] = "ResidentialStateCode"
        [2] = "CountOfClientCode"
    Values = a string array of:
        [0] = a string array of:
            [0] = "Melbourne"
            [1] = "Vic"
            [2] = "13"
        [1] = a string array of:
            [0] = "Launceston"
            [1] = "Tas"
            [2] = "4"

VT_API Methods